package com.uottawa.library.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.uottawa.library.domain.User;

/**
 * Data Access Object (DB Agent)
 * 
 * @author User
 * 
 */
public class UserDAO extends BaseDAO {

	public boolean addUser(User u) {
		String query = "INSERT INTO USER (USERNAME,PASSWORD, ROLE) VALUES (?, ?, ?)"; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			stmt.setString(1, u.getUsername());
			stmt.setString(2, u.getPassword());
			stmt.setString(3, u.getRole());

			stmt.executeUpdate();
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}

	public boolean removeUser(User u) {
		String query = "DELETE FROM USER WHERE USERID = '" + u.getUserId() + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}
	public double getUserBalance(int userId)
	{

		double userBalance = 0;
		String query = "Select SUM(LATE_FEES) as SUM FROM LOAN WHERE USERID = '" + userId + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				userBalance= rs.getInt("SUM");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return userBalance;
		
	}
//;
	public boolean setUserBalance(User user) {
		String query = "UPDATE USER SET BALANCE ='"+user.getBalance()+"' WHERE USERID = '" + user.getUserId() + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}

	public boolean updateUserBalance() {
		String query = "UPDATE LOAN SET LATE_FEES = 10 WHERE expiry_date < now() AND RETURNED =0"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}
	
	
	public int getActiveLoans(User user) {
		int activeLoans = 0;
		
		String query = "Select COUNT(1) as count FROM LOAN WHERE USERID = " + user.getUserId() +" and expiry_date > NOW() AND RETURNED = 0" ; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				activeLoans = rs.getInt("count");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return activeLoans;
	}
	
	public int getExpiredLoans(User user) {
		int expiredLoans = 0;
		
		String query = "Select COUNT(1) as count FROM LOAN WHERE USERID =" + user.getUserId() + " and expiry_date < NOW() AND RETURNED = 0" ; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				expiredLoans = rs.getInt("count");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return expiredLoans;
	}

	public User getUser(int userId) {
		User user = null;
		String query = "Select * FROM USER WHERE USERID = '" + userId + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
				user = new User();
				user.setRole(rs.getString("role"));
				user.setBalance(rs.getInt("balance"));
				
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return user;
	}
	public User getUser(String username, String password) {
		User user = null;
		String query = "Select * FROM USER WHERE USERNAME = '" + username + "' AND PASSWORD = '" + password + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
				user = new User();
				user.setUserId(rs.getInt("USERID"));
				user.setUsername(username);
				user.setPassword(rs.getString("password"));
				user.setRole(rs.getString("role"));
				user.setBalance(rs.getInt("balance"));
				
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return user;
	}

	public List<User> getAllUser() {
		List<User> userArryList = null;
		String query = "Select * FROM USER"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				userArryList = new ArrayList<User>();
				while(rs.next())
				{
				User user = new User();
				user.setUserId(rs.getInt("USERID"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setRole(rs.getString("role"));
				user.setBalance(rs.getInt("balance"));
				userArryList.add(user);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userArryList;
	}

	public boolean removeUser(int userId) {
		
		String query = "DELETE FROM USER WHERE USERID = '" + userId + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	public int getActiveLoans(int userId) {
		
	int activeLoans = 0;
		
		String query = "Select COUNT(1) as count FROM LOAN WHERE USERID = " + userId +" and expiry_date > NOW() " ; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				activeLoans = rs.getInt("count");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return activeLoans;
	}

}
